﻿Imports BUS
Public Class BCTongKetHocKy

    Private Sub BCTongKetHocKy_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ' tao gia tri cho cmb Hoc Ky
        Dim i As Integer
        For i = 1 To 2
            Me.cmbHocKy.Items.Add(i)
        Next

        'load du lieu len cmb Nam
        Dim BangNH As New DataTable
        Dim nhBus As New NamHocBUS
        BangNH = nhBus.LayBang()
        Me.cmbNamHoc.DataSource = BangNH
        Me.cmbNamHoc.DisplayMember = "Nam"
        Me.cmbNamHoc.ValueMember = "MaNH"

        Me.rdK10.Checked = True

    End Sub

    Private Sub btnDongY_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDongY.Click
        Try
            Dim lhBus As New LopHocBUS
            Dim bang As New DataTable
            Dim maKhoi As Integer

            If (Me.rdK10.Checked = True) Then
                maKhoi = 1
            ElseIf (Me.rdK11.Checked = True) Then
                maKhoi = 2
            ElseIf (Me.rdK12.Checked = True) Then
                maKhoi = 3
            Else
                maKhoi = 0
            End If

            Dim nam As Integer
            nam = Integer.Parse(Me.cmbNamHoc.SelectedValue)

            Dim hocKy As Integer
            hocKy = Me.cmbHocKy.SelectedValue + 1

            Dim dtbBus As New DiemTrungBinhBUS

            Dim bangDTB As DataTable
            Dim bangLH As DataTable
            Dim mang() As Integer


            If (maKhoi = 0) Then



                bang = lhBus.LayLHTheoTCKhoi(nam)
                Me.gridKetQua.DataSource = bang

                bangLH = lhBus.SoLH_TatCa(nam)

                Array.Resize(mang, bangLH.Rows.Count)

                Dim i As Integer
                For i = 0 To bangLH.Rows.Count - 1
                    bangDTB = dtbBus.DiemTB_TatCa(nam, hocKy)
                    Dim dem As Integer
                    dem = 0
                    For Each dong As DataRow In bangDTB.Rows

                        If (dong.Item("DiemTrungBinhHocKy") >= 5 And dong.Item("TenLH") = bangLH.Rows(i).Item("TenLH")) Then
                            dem = dem + 1
                        End If
                    Next

                    mang(i) = dem

                Next

                bang.Columns.Add("Số Lượng Đạt")
                bang.Columns.Add("Tỷ Lệ (%)")

                For Each dong As DataRow In bang.Rows

                    dong.Item("Số Lượng Đạt") = mang(bang.Rows.IndexOf(dong))

                Next

                For Each dong As DataRow In bang.Rows
                    Dim tyLe As Double
                    tyLe = Math.Round((dong.Item("Số Lượng Đạt") / dong.Item("SiSo")) * 100, 2)
                    dong.Item("Tỷ Lệ (%)") = tyLe.ToString()
                Next

                Me.gridKetQua.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                Me.gridKetQua.Columns(0).HeaderText = "Tên Lớp"
                Me.gridKetQua.Columns(1).HeaderText = "Sĩ Số"
                Me.gridKetQua.Columns(0).Width = 200
                Me.gridKetQua.Columns(1).Width = 150
                Me.gridKetQua.Columns(2).Width = 150
                Me.gridKetQua.Columns(3).Width = 150



            Else
                bang = lhBus.LayLHTheoKhoi(maKhoi, nam)
                Me.gridKetQua.DataSource = bang

                bangLH = lhBus.SoLH(maKhoi, nam)

                Array.Resize(mang, bangLH.Rows.Count)

                Dim i As Integer
                For i = 0 To bangLH.Rows.Count - 1
                    bangDTB = dtbBus.TinhDiemTrungBinh(bangLH.Rows(i)(0), nam, hocKy)
                    Dim dem As Integer
                    dem = 0
                    For Each dong As DataRow In bangDTB.Rows

                        If (dong.Item("DiemTrungBinhHocKy") >= 5) Then
                            dem = dem + 1
                        End If
                    Next

                    mang(i) = dem

                Next


                bang.Columns.Add("Số Lượng Đạt")
                bang.Columns.Add("Tỷ Lệ (%)")

                'Dim j As Integer
                For Each dong As DataRow In bang.Rows

                    dong.Item("Số Lượng Đạt") = mang(bang.Rows.IndexOf(dong))

                Next

                For Each dong As DataRow In bang.Rows
                    Dim tyLe As Double
                    tyLe = Math.Round((dong.Item("Số Lượng Đạt") / dong.Item("SiSo")) * 100, 2)
                    dong.Item("Tỷ Lệ (%)") = tyLe.ToString()
                Next
                Me.gridKetQua.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                Me.gridKetQua.Columns(0).HeaderText = "Tên Lớp"
                Me.gridKetQua.Columns(1).HeaderText = "Sĩ Số"
                Me.gridKetQua.Columns(0).Width = 200
                Me.gridKetQua.Columns(1).Width = 150
                Me.gridKetQua.Columns(2).Width = 150
                Me.gridKetQua.Columns(3).Width = 150

            End If
        Catch ex As Exception

        End Try
    End Sub

    Private Sub btnXuatFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnXuatFile.Click
        Try
            Dim app As New Microsoft.Office.Interop.Excel.Application()
            app.Visible = False
            Dim workbooks As Microsoft.Office.Interop.Excel.Workbooks
            workbooks = app.Workbooks
            Dim book As Microsoft.Office.Interop.Excel._Workbook
            book = workbooks.Add(Type.Missing)
            Dim sheet As Microsoft.Office.Interop.Excel._Worksheet
            sheet = book.ActiveSheet

            Dim dt As DataTable
            dt = Me.gridKetQua.DataSource
            sheet.Range("A1", Type.Missing).Value2 = "Tên Lớp"
            sheet.Range("B1", Type.Missing).Value2 = "Sĩ Số"
            sheet.Range("C1", Type.Missing).Value2 = "Số Lượng Đạt"
            sheet.Range("D1", Type.Missing).Value2 = "Tỷ Lệ (%)"

            Dim colIndex = 1
            Dim j As Integer = 2

            For i As Integer = 0 To dt.Rows.Count - 1
                Dim dr As DataRow
                dr = dt.Rows(i)
                sheet.Range("A" + j.ToString(), Type.Missing).Value2 = dr(0).ToString()
                sheet.Range("B" + j.ToString(), Type.Missing).Value2 = dr(1).ToString()
                sheet.Range("C" + j.ToString(), Type.Missing).Value2 = dr(2).ToString()
                sheet.Range("D" + j.ToString(), Type.Missing).Value2 = dr(3).ToString()
                j = j + 1
            Next
            Me.SaveFileDialog1.ShowDialog()
            Dim name = Me.SaveFileDialog1.FileName
            book.SaveAs(name, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
        Catch ex As Exception

        End Try

    End Sub
End Class
